﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class TuyenBayDAO
    {
        Database db;
        SqlConnection sql;
        DataTable dt;

        public TuyenBayDAO()
        {
            db = new Database();
            sql = db.getConnection();
        }

        public DataTable LayDSTuyenBay()
        {
            dt = db.Execute("Select * FROM TUYENBAY ");
            return dt;
        }
        public void Them(TuyenBayDTO ma)
        {

            String Ma = ma.Matuyenbay;
            String SBDi = ma.Sanbaydi;
            String SBDen = ma.Sanbayden;
            String strSQL = "INSERT INTO TUYENBAY VALUES('" + Ma + "','" + SBDi + "','" + SBDen + "')";
            db.ExecuteNonQuery(strSQL);
        }

        public void Xoa(int index)
        {
            string sql = "delete from TUYENBAY where MATUYENBAY ='" + dt.Rows[index]["MATUYENBAY"].ToString() + "'";//phai nho co dao nhay ' ko thi bi bao loi invalid column name
            db.ExecuteNonQuery(sql);
            dt.Rows.RemoveAt(index);

        }
        public void CapNhat(TuyenBayDTO sbdto)
        {
            SqlCommand updatecmd = new SqlCommand("UpdateTB_Proc", sql);
            updatecmd.Connection = sql;
            updatecmd.CommandType = CommandType.StoredProcedure;

            SqlParameter parMaTB = new SqlParameter("@MaTB", SqlDbType.NVarChar, 10);
            SqlParameter parSBDi = new SqlParameter("@SBDi", SqlDbType.NVarChar, 10);
            SqlParameter parSBDen = new SqlParameter("@SBDen", SqlDbType.NVarChar, 10);

            parMaTB.Value = sbdto.Matuyenbay;
            parSBDi.Value = sbdto.Sanbaydi;
            parSBDen.Value = sbdto.Sanbayden;

            updatecmd.Parameters.Add(parMaTB);
            updatecmd.Parameters.Add(parSBDi);
            updatecmd.Parameters.Add(parSBDen);
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.UpdateCommand = updatecmd;
            sql.Open();

            updatecmd.ExecuteNonQuery();
            sql.Close();
        }
    }
}
